﻿CREATE OR REPLACE FUNCTION trgfnupdate_approved_quotation()
                        RETURNS trigger AS
                        $BODY$
                        Begin
                            If TG_OP='UPDATE' then
                                if new.approved_amount<>old.approved_amount then
                                    if new.approved_amount=0 or new.approved_amount is null then
                                        update sale_order set negotiating='Negotiated' where id=new.id;
                                    else
                                        update sale_order set negotiating='Fixed' where id=new.id;
                                    end if;        
                                end if;
				if new.amount<>old.amount or new.amount_tax <> old.amount_tax then
					update sale_order set amount_total=new.amount+new.amount_tax where id=new.id;
				end if;                                
                                if new.q_amount_e<>old.q_amount_e or new.q_amount_e_other <> old.q_amount_e_other or new.q_amount_e_discount<>old.q_amount_e_discount then
					update sale_order set q_amount_total_e=new.q_amount_e+new.q_amount_e_other+q_amount_e_discount where id=new.id;
				end if;
                                if new.q_amount_m<>old.q_amount_m or new.q_amount_m_other <> old.q_amount_m_other or new.q_amount_m_discount<>old.q_amount_m_discount then
					update sale_order set q_amount_total_m=new.q_amount_m+new.q_amount_m_other+q_amount_m_discount where id=new.id;
				end if;				
                            end if;
                            if TG_OP='INSERT' then
                                if new.approved_amount>0 then
                                    update sale_order set negotiating='Fixed' where id=new.id;
                                else
                                    update sale_order set negotiating='Negotiated' where id=new.id;
                                end if;
                            end if;
                        Return new;
                        end;    
                        $BODY$
                      LANGUAGE plpgsql VOLATILE
                      COST 100;
CREATE TRIGGER trgchange_approved_amount
                      AFTER INSERT OR UPDATE
                      ON sale_order
                      FOR EACH ROW
                      EXECUTE PROCEDURE trgfnupdate_approved_quotation();